package com.app.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.app.application.MyApplication;
import com.app.config.AppConfig;
import com.core.lib.utils.main.LogUtilBase;
import com.core.lib.utils.main.StringUtilBase;
import com.eyzhs.app.R;
import com.lepu.app.fun.my.bean.BeanCollectKnowledge;

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHelper";

    public static final String DATABASE_NAME_ALBUM = "main.db";
    public static final String DATABASE_NAME_CITY = "city.db";
    public static final String DATABASE_NAME_TOOL = "tool.db";

    // 数据库版本号
    public static final int DATABASE_VERSION = 6;//5

    private static Context mContext = null;
    public static Object mLockObj = new Object();

    private String mDbName = "";

    public static DBHelper getInstance(String dbName) {
        if (mContext == null) {
            mContext = MyApplication.getInstance();
        }
        return new DBHelper(dbName);
    }

    private DBHelper(String dbName) {
        super(mContext, dbName, null, DATABASE_VERSION);
        mDbName = dbName;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        LogUtilBase.LogD(null, "onCreate");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            LogUtilBase.LogD(null, "onUpgrade==oldVersion==>>" + oldVersion
                    + "newVersion=>>" + newVersion);
            // 程序升级，更新为true
            AppConfig.setConfig(mContext, Const.CONFIG_APP_UPGRADE_USE, true);

            for (int i = (oldVersion + 1); i <= newVersion; i++)
                upgrade(db, i);
        }
    }

    @Override
    protected void finalize() throws Throwable {
        if (this != null)
            this.close();
        super.finalize();
    }

    /*
     * 升级程序时，修改了数据库，需要在以下用sql语句去修改
     */
    private void upgrade(SQLiteDatabase db, int version) {
        LogUtilBase.LogD(TAG, version + "");

        //数据库升级总是抛数据库被锁异常，暂时不在这儿做数据库升级
//        switch (version) {
//            case 1:
//                break;
//            case 2:
//                // 程序升级使用
//                if (mDbName.equals(DATABASE_NAME_ALBUM)) {
//                    db.execSQL("alter table CollectHistory add column Title TEXT");
//                    db.execSQL("alter table CollectHistory add column SubTitle TEXT");
//                    db.execSQL("alter table CollectHistory add column ArticleID TEXT");
//                }
//                break;
//            case 3:
//                if (mDbName.equals(DATABASE_NAME_ALBUM)) {
//                    String sql = mContext.getString(R.string.create_table_my_message);
//                    db.execSQL(sql);
//                }
//                break;
//            case 4:
//                if (mDbName.equals(DATABASE_NAME_ALBUM)) {
//                    String sqlCreatChatMessage = (String) mContext
//                            .getText(R.string.create_table_chat_message);
//                    String sqlCreatChatSession = (String) mContext
//                            .getText(R.string.create_table_chat_session);
//                    String sqlCreatDoctor = (String) mContext
//                            .getText(R.string.create_table_doctor);
//                    db.execSQL(sqlCreatChatMessage);
//                    db.execSQL(sqlCreatChatSession);
//                    db.execSQL(sqlCreatDoctor);
//                }
//                break;
//            case 7:
//                if (mDbName.equals(DATABASE_NAME_ALBUM)) {
//                    String sql = mContext.getString(R.string.create_table_test);
//                    db.execSQL(sql);
//
//                    db.execSQL("delete from CollectHistory where Type=?;", new Object[]{String.valueOf(BeanCollectKnowledge.TYPE_VIDEO_OLD)});
//                    db.execSQL("delete from CollectHistory where Type=?;", new Object[]{String.valueOf(BeanCollectKnowledge.TYPE_ARTICLE_OLD)});
//
//                    LogUtilBase.LogD(TAG, "db upgrade version 6 success");
//                }
//                break;
//            default:
//                break;
//        }
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }

    public static void checkDatabase(Context context) {
        synchronized (mLockObj) {
            mContext = context;
            DBHelper helper1 = getInstance(DATABASE_NAME_ALBUM);
            if (helper1 != null) {
                helper1.createDatabase();
                helper1.close();
            }

            DBHelper helper2 = getInstance(DATABASE_NAME_CITY);
            if (helper2 != null) {
                helper2.createDatabase();
                helper2.close();
            }

            DBHelper helper3 = getInstance(DATABASE_NAME_TOOL);
            if (helper3 != null) {
                helper3.createDatabase();
                helper3.close();
            }
        }
    }

    /**
     * 创建数据库
     */
    private void createDatabase() {
        String dbPath = SdLocal.getDatabasePath(mContext, mDbName);

        File pathFile = new File(dbPath);
        // 为了解决程序向下降级而加
        // int oldVersion = getDatabaseVersion();
        // if(oldVersion > DATABASE_NEW_VERSION)
        // {
        // FileUtilBase.deleteFile(pathFile);
        // LogUtilBase.LogD(TAG, "删除旧的数据库");
        //
        // //清除用户配置文件数据
        // LoginUtil.clearLoginInfo(context);
        // PedometerUtil.clearInfo(context);
        // }
        if (!pathFile.exists()) {
            try {
                SQLiteDatabase db = this.getReadableDatabase();
                db.close();
                copyDB();
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            }
        }
    }

    private void copyDB() {
        String dbPath = SdLocal.getDatabasePath(mContext, mDbName);
        int id = 0;
        if (mDbName.equals(DATABASE_NAME_ALBUM))
            id = R.raw.main;
        else if (mDbName.equals(DATABASE_NAME_CITY))
            id = R.raw.city;
        else if (mDbName.equals(DATABASE_NAME_TOOL))
            id = R.raw.tool;

        InputStream is = null;
        FileOutputStream fos = null;
        try {
            is = mContext.getResources().openRawResource(id);
            fos = new FileOutputStream(dbPath);
            byte[] buffer = new byte[8192];
            int count = 0;
            while ((count = is.read(buffer)) > 0) {
                fos.write(buffer, 0, count);
            }

        } catch (Exception e) {
            LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
        } finally {
            try {
                if (fos != null)
                    fos.close();
                if (is != null)
                    is.close();
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            }

        }
    }

    // ================
    public void updateVersion() {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = SdLocal.getDatabasePath(mContext, mDbName);
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);

            checkDB.beginTransaction();
            int oldVersion = checkDB.getVersion();
            checkDB.setVersion(DATABASE_VERSION);
            checkDB.setTransactionSuccessful();

            int newVersion = checkDB.getVersion();
            LogUtilBase.LogD(TAG, "oldVersion=" + oldVersion);
            LogUtilBase.LogD(TAG, "newVersion=" + newVersion);

        } catch (SQLiteException e) {
            LogUtilBase.LogD(TAG, Log.getStackTraceString(e));
        } finally {
            if (checkDB != null) {
                checkDB.endTransaction();
                checkDB.close();
            }
        }
    }

    public int getDatabaseVersion() {
        SQLiteDatabase checkDB = null;
        int version = 0;
        try {
            String myPath = SdLocal.getDatabasePath(mContext, mDbName);
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);
            version = checkDB.getVersion();
        } catch (SQLiteException e) {
            LogUtilBase.LogD(TAG, Log.getStackTraceString(e));
        } finally {
            if (checkDB != null) {
                checkDB.close();
            }
        }

        return version;
    }

    // ==========================================================

    public boolean isTableExists(String tableName) {
        String sql = "select count(*) from sqlite_master where type='table' and name=?";
        String result = executeScalar(sql, new String[]{tableName});
        return !StringUtilBase.stringIsEmpty(result)
                && Integer.parseInt(result) == 1;
    }

    public String executeScalar(String sql, String[] args) {
        ArrayList<HashMap<String, Object>> resultArray = query(sql, args);
        if (resultArray.size() > 0) {
            HashMap<String, Object> json = resultArray.get(0);
            Iterator<Object> it = json.values().iterator();
            while (it.hasNext()) {
                return (String) it.next();
            }
        }
        return "";
    }

    public synchronized boolean isColumeExists(String tableName,
                                               String columnName) {
        boolean result = false;
        Cursor cursor = null;
        SQLiteDatabase db = null;
        try {
            db = this.getReadableDatabase();
            // 查询一行
            cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0",
                    null);
            result = cursor != null && cursor.getColumnIndex(columnName) != -1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (cursor != null)
                    cursor.close();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            try {
                if (db != null)
                    db.close();
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            }
        }

        return result;
    }

    /*
     * 查询
     */
    public ArrayList<HashMap<String, Object>> query(String sql,
                                                    String[] selectionArgs) {
        ArrayList<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        synchronized (mLockObj) {
            try {
                db = this.getReadableDatabase();
                cursor = db.rawQuery(sql, selectionArgs);
                while (cursor.moveToNext()) {
                    HashMap<String, Object> map = new HashMap<String, Object>();
                    int count = cursor.getColumnCount();
                    for (int i = 0; i < count; i++) {
                        map.put(cursor.getColumnName(i),
                                getColumnValue(cursor, i));
                    }
                    result.add(map);
                }
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (cursor != null)
                        cursor.close();
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return result;
    }

    private Object getColumnValue(Cursor cursor, int index) {
        Object result = null;
        try {
            result = cursor.getString(index);
        } catch (Exception e) {

        }
        return result;
    }

    public boolean executeIsExists(String sql, String[] argsArray) {
        boolean exists = false;
        SQLiteDatabase db = null;
        Cursor cursor = null;
        synchronized (mLockObj) {
            try {
                db = this.getReadableDatabase();
                cursor = db.rawQuery(sql, argsArray);
                exists = cursor.moveToFirst();
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (cursor != null)
                        cursor.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return exists;
    }

    public boolean executeNonQuery(String sql, Object[] args) {
        SQLiteDatabase db = null;
        synchronized (mLockObj) {
            try {
                db = getWritableDatabase();
                db.execSQL(sql, args);
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return true;
    }

    public boolean executeNonQuery(String sql) {
        SQLiteDatabase db = null;
        synchronized (mLockObj) {
            try {
                db = getWritableDatabase();
                db.execSQL(sql);
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return true;
    }

    /*
     * insert
     */
    public boolean insert(String table, String nullColumnHack,
                          ContentValues values) {
        SQLiteDatabase db = null;
        boolean flag = false;

        synchronized (mLockObj) {
            try {
                db = getWritableDatabase();
                long count = db.insert(table, nullColumnHack, values);
                flag = count > 0;
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return flag;
    }

    /*
     * delete
     */
    public boolean update(String table, ContentValues values,
                          String whereClause, String[] whereArgs) {
        SQLiteDatabase db = null;
        boolean flag = false;

        synchronized (mLockObj) {
            try {
                db = getWritableDatabase();
                long count = db.update(table, values, whereClause, whereArgs);
                flag = count > 0;
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return flag;
    }

    /*
     * delete
     */
    public boolean delete(String table, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = null;
        boolean flag = false;

        synchronized (mLockObj) {
            try {
                db = getWritableDatabase();
                long count = db.delete(table, whereClause, whereArgs);
                flag = count > 0;
            } catch (Exception e) {
                LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
            } finally {
                try {
                    if (db != null)
                        db.close();
                } catch (Exception e) {
                    LogUtilBase.LogD("Exception", Log.getStackTraceString(e));
                }
            }
        }

        return flag;
    }

}
